package net.playcabbage.database.operations;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.TimeZone;
import java.util.TreeMap;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.sql.DataSource;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import net.playcabbage.beans.CheckinBean2;
import net.playcabbage.utility.CabbageStringsUtility;


import org.codehaus.jettison.json.JSONArray;
import org.codehaus.jettison.json.JSONException;
import org.codehaus.jettison.json.JSONObject;
import org.w3c.dom.Document;
import org.w3c.dom.Element;


public class CabbageConnection {

	public CabbageConnection() {

	}

	public CheckinBean2 checkin(String urlString) throws SQLException {
		if ((validateCheckin(urlString))) {
			Connection con = getConnection();
			PreparedStatement preparedStatement = 
					con.prepareStatement("insert into checkin (urlstring) values (?)",Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setString(1, "superid=" + urlString);
			preparedStatement.execute();

			ResultSet rskey = preparedStatement.getGeneratedKeys();

			if (rskey != null && rskey.next()) {
				return getCheckinBean(rskey.getInt(1));
			}

			con.close();

		}
		return null;
	}

	private CheckinBean2 getCheckinBean(int id) throws SQLException {

		Connection con = getConnection();
		PreparedStatement preparedStatement = 
				con.prepareStatement("SELECT cm.name,l.urlstring,c.mytimestamp " +
						"FROM collectiblemaster cm " +
						"INNER JOIN livecollectible lc " +
						"ON cm.collectiblemaster_id=lc.collectiblemaster_id " +
						"INNER JOIN location l " +
						"ON lc.location_id=l.location_id " + 
						"INNER JOIN checkin c " +
						"ON l.urlstring=c.urlstring " +
						"WHERE c.checkin_id=?");
		preparedStatement.setInt(1, id);
		preparedStatement.execute();
		ResultSet rs = preparedStatement.getResultSet();

		CheckinBean2 cb = null;
		while(rs.next()) {
			String collectibleName = rs.getString("cm.name");
			String uString = rs.getString("l.urlString");
			String ts = localiseTimestamp(rs.getTimestamp("mytimestamp"));
			cb = new CheckinBean2(uString,ts.toString(),collectibleName);
		}

		con.close();

		return cb;
	}

	private String localiseTimestamp(Timestamp ts) {

		// Declare the Locale (of the mysql instance).
		Locale remoteLocal = new Locale("en_US");
		// Declare the TimeZone (of the mysql instance);
		TimeZone remoteTimeZone = TimeZone.getTimeZone("EST");
		// Get a Calendar object which is set to the above.
		Calendar remoteCal = Calendar.getInstance(remoteTimeZone, remoteLocal);
		// Set Calendar time using the timestamp.
		remoteCal.setTime(ts);
		// Add 10 from Calendar hours (EST to Australia/Melbourne); 
		remoteCal.add(Calendar.HOUR, +10);

		//Timestamp newts = (Timestamp) remoteCal.getTime();

		return remoteCal.getTime().toString();
	}

	//	public void checkin(String urlString) throws SQLException, NamingException {
	//		if ((validateCheckin(urlString))) {
	//			Connection con = getConnection();
	//			PreparedStatement preparedStatement = 
	//					con.prepareStatement("insert into checkin (urlstring) values (?)");
	//			preparedStatement.setString(1, urlString);
	//			preparedStatement.execute();
	//			con.close();
	//		}
	//	}

	private boolean validateCheckin(String urlString) throws SQLException {

		char c = '=';
		int l = 7;

		//if (! CabbageStringsUtility.isCharacter(c,urlString.charAt(7))) return false;
		if (! (CabbageStringsUtility.correctLength(urlString,l))) return false;
		if (! CabbageStringsUtility.isNumeric(urlString)) return false;
		if (! CabbageStringsUtility.isInBounds(urlString.substring(0, 2),1,153)) return false;
		if (! CabbageStringsUtility.isInBounds(urlString.substring(2, 4),1,88)) return false;
		if (! CabbageStringsUtility.isValidZParameter(urlString.substring(4, 6),00,40)) return false;
		if (! validateCheckpoint(urlString)) return false;
		return true;

		//		char c = '=';
		//		int l = 7;
		//
		//		if (! CabbageStringsUtility.isCharacter(c,urlString.charAt(7))) return false;
		//		if (! (CabbageStringsUtility.correctLength(urlString.substring(8),l))) return false;
		//		if (! CabbageStringsUtility.isNumeric(urlString.substring(8, 15))) return false;
		//		if (! CabbageStringsUtility.isInBounds(urlString.substring(8, 11),1,153)) return false;
		//		if (! CabbageStringsUtility.isInBounds(urlString.substring(11, 13),1,88)) return false;
		//		if (! CabbageStringsUtility.isValidZParameter(urlString.substring(13, 15),00,40)) return false;
		//		if (! validateCheckpoint(urlString)) return false;
		//		return true;
	}

	private boolean validateCheckpoint(String urlString) throws SQLException {
		Connection con = getConnection();
		PreparedStatement preparedStatement = 
				con.prepareStatement("SELECT COUNT(*) from location l INNER JOIN checkpoint c ON l.location_id=c.location_id WHERE l.urlstring=?");
		preparedStatement.setString(1, "superid=" + urlString);
		int count = 0;
		if (! preparedStatement.execute()) {
			return false;
		} else {
			ResultSet rs = preparedStatement.getResultSet();
			while (rs.next()) {
				if (rs.getInt(1) == 1) {
					count ++;
				}
			}
		}
		con.close();

		if (count == 1) {
			return true;
		} else {
			return false;
		}
	}

	public List<String> getLastCheckins(int numOfRecords, boolean asJSON) throws SQLException {
		return lastCheckins(true,numOfRecords);
	}

	public JSONArray lastCheckins(boolean limitNum,int numOfRecords, boolean asJSON) throws SQLException, JSONException {
		JSONArray jArray = null;
		String sqlString = "select * from checkin ORDER BY checkin_id DESC";
		if (limitNum == true) {
			sqlString = "select * from checkin ORDER BY checkin_id DESC LIMIT " + Integer.toString(numOfRecords);
		}
		Connection connection = getConnection();
		List<String> rList = null;
		List<String> urlStringList = null;
		PreparedStatement preparedStatement = connection.prepareStatement(sqlString);
		if (preparedStatement.execute()) {

			ResultSet rs = preparedStatement.getResultSet();
			ResultSetMetaData rsmd = rs.getMetaData();

			//			rList = new ArrayList<String>();
			//			urlStringList = new ArrayList<String>();
			//			while (rs.next() != false){
			//				String urlstring = rs.getString("urlstring");
			//				rList.add(urlstring);
			//				urlStringList.add(urlstring.substring(8));
			//
			//				Timestamp timestamp = rs.getTimestamp("mytimestamp");
			//				// Declare the Locale (of the mysql instance).
			//				Locale remoteLocal = new Locale("en_US");
			//				// Declare the TimeZone (of the mysql instance);
			//				TimeZone remoteTimeZone = TimeZone.getTimeZone("EST");
			//				// Get a Calendar object which is set to the above.
			//				Calendar remoteCal = Calendar.getInstance(remoteTimeZone, remoteLocal);
			//				// Set Calendar time using the timestamp.
			//				remoteCal.setTime(timestamp);
			//				// Add 10 from Calendar hours (EST to Australia/Melbourne); 
			//				remoteCal.add(Calendar.HOUR, +10);
			//				rList.add(remoteCal.getTime().toString());
			//			}

			jArray = makeJSONArray(rsmd, rs);

		}
		connection.close();
		//return rList;

		return jArray;
	}

	public JSONArray lastCheckinsReal(boolean limitNum,int numOfRecords, boolean asJSON) throws SQLException, JSONException {
		JSONArray jArray = null;
		String sqlString = "select * from checkin ORDER BY checkin_id DESC";
		if (limitNum == true) {
			sqlString = "select * from checkin ORDER BY checkin_id DESC LIMIT " + Integer.toString(numOfRecords);
		}
		Connection connection = getConnection();
		List<String> rList = null;
		List<String> urlStringList = null;
		PreparedStatement preparedStatement = connection.prepareStatement(sqlString);
		if (preparedStatement.execute()) {

			ResultSet rs = preparedStatement.getResultSet();
			ResultSetMetaData rsmd = rs.getMetaData();

			jArray = makeJSONArray(rsmd, rs);

		}
		connection.close();

		return jArray;
	}

	public List<String> getLastCheckins(int numOfRecords) throws SQLException {
		return lastCheckins(true,numOfRecords);
	}

	public List<String> getLastCheckins() throws SQLException {
		return lastCheckins(false,0);
	}



	private List<String> lastCheckins(boolean limitNum,int numOfRecords) throws SQLException {

		String sqlString = "select * from checkin ORDER BY checkin_id DESC";
		if (limitNum == true) {
			sqlString = "select * from checkin ORDER BY checkin_id DESC LIMIT " + Integer.toString(numOfRecords);
		}
		Connection connection = getConnection();
		List<String> rList = null;
		List<String> urlStringList = null;
		PreparedStatement preparedStatement = connection.prepareStatement(sqlString);
		if (preparedStatement.execute()) {
			ResultSet rs = preparedStatement.getResultSet();
			rList = new ArrayList<String>();
			urlStringList = new ArrayList<String>();
			while (rs.next() != false){
				String urlstring = rs.getString("urlstring");
				rList.add(urlstring);
				urlStringList.add(urlstring.substring(8));

				//rList.add(rs.getString("urlstring"));
				//String urls = rs.getString("urlstring");
				// Get the timestamp column in the resultset.
				Timestamp timestamp = rs.getTimestamp("mytimestamp");
				// Declare the Locale (of the mysql instance).
				Locale remoteLocal = new Locale("en_US");
				// Declare the TimeZone (of the mysql instance);
				TimeZone remoteTimeZone = TimeZone.getTimeZone("EST");
				// Get a Calendar object which is set to the above.
				Calendar remoteCal = Calendar.getInstance(remoteTimeZone, remoteLocal);
				// Set Calendar time using the timestamp.
				remoteCal.setTime(timestamp);
				// Add 10 from Calendar hours (EST to Australia/Melbourne); 
				remoteCal.add(Calendar.HOUR, +10);
				rList.add(remoteCal.getTime().toString());
			}
		}
		connection.close();
		//return rList;
		Map<Integer,List<String>> cl = getCollectiblesFromCheckins();
		return consolidateLists(rList,cl);
	}

	private List<String> consolidateLists(List<String> checkinList,Map<Integer,List<String>> collList) {

		List<String> newList = new ArrayList<String>();

		for (String s : checkinList) {
			newList.add(s);

			String stripped = s.substring(8);

			if(CabbageStringsUtility.isNumeric(stripped)) {
				int loc = Integer.parseInt(stripped);

				String ss = null;
				if(collList.containsKey(loc)) {
					List<String> il = collList.get(loc);

					for (String is : il) {
						if (ss == null) {
							ss = is + " ";
						} else {
							ss += is + " ";
						}
					}

				}

				if (ss != null) {
					newList.add(ss);
				} else {
					newList.add("? mystery ? (unfound) collectible");
				}

			}
		}

		return newList;

	}


	public Map<Integer,List<String>> getCollectiblesFromCheckins() throws SQLException {
		String sqlString = "SELECT cm.name,l.urlstring " +
				"FROM collectiblemaster cm " +
				"INNER JOIN livecollectible lc " +
				"ON cm.collectiblemaster_id=lc.collectiblemaster_id " +
				"INNER JOIN location l " +
				"ON lc.location_id=l.location_id ";

		Connection connection = getConnection();
		PreparedStatement preparedStatement = connection.prepareStatement(sqlString);


		preparedStatement.execute();

		ResultSet rs = preparedStatement.getResultSet();

		Map<Integer,List<String>> cMap = new TreeMap<Integer,List<String>>();
		while(rs.next()) {
			String name = rs.getString("cm.name");
			String urlstr = rs.getString("l.urlstring");
			String stripped = urlstr.substring(8);
			int loc = Integer.parseInt(stripped);
			if(cMap.containsKey(loc)) {
				cMap.get(loc).add(name);
			} else {
				List<String> l = new ArrayList<String>();
				l.add(name);
				cMap.put(loc,l);
			}
		}

		connection.close();

		return cMap;
	}

	public List<String> getPageList() throws NamingException {
		List<String> l = new ArrayList<String>();
		int colCount;
		try {
			Connection connection = getConnection();
			PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM pages INNER JOIN pagecategory ON pages.pagecategoryID=pagecategory.pagecategoryID");
			if (preparedStatement.execute()) {
				ResultSet rs = preparedStatement.getResultSet();
				ResultSetMetaData md = rs.getMetaData();
				colCount = md.getColumnCount();
				while(rs.next() != false) {
					String current = "Result...";
					for (int x = 1; x <= colCount; x ++) {
						current += md.getColumnName(x) + ": " + rs.getString(x) + " ";
					}
					l.add(current);
				}
			}

			connection.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return l;
	}

	public List<String> getPagePathList() throws NamingException {
		List<String> l = new ArrayList<String>();
		int colCount;
		try {
			Connection connection = getConnection();
			PreparedStatement preparedStatement = connection.prepareStatement("SELECT pages.pagename as pagename, pagecategory.pagecategoryname as catname FROM pages INNER JOIN pagecategory ON pages.pagecategoryID=pagecategory.pagecategoryID");
			if (preparedStatement.execute()) {
				ResultSet rs = preparedStatement.getResultSet();
				ResultSetMetaData md = rs.getMetaData();
				colCount = md.getColumnCount();
				while(rs.next() != false) {
					String current = "/";
					current += rs.getString("catname") + "/" + rs.getString("pagename");
					l.add(current);
				}
			}

			connection.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return l;
	}

	public JSONArray getMasterGridListJSON() throws NamingException, SQLException, JSONException {
		JSONArray jArray = new JSONArray();
		Connection connection = getConnection();
		PreparedStatement preparedStatement = connection.prepareStatement("select * from gridcellmaster limit 100");

		if (preparedStatement.execute()) {
			ResultSet rs = preparedStatement.getResultSet();
			ResultSetMetaData rsmd = rs.getMetaData();

			while(rs.next()) {
				int numColumns = rsmd.getColumnCount();
				JSONObject obj = new JSONObject();

				for (int i=1; i<numColumns+1; i++) {
					String column_name = rsmd.getColumnName(i);

					if(rsmd.getColumnType(i)==java.sql.Types.ARRAY){
						obj.put(column_name, rs.getArray(column_name));
					}
					else if(rsmd.getColumnType(i)==java.sql.Types.BIGINT){
						obj.put(column_name, rs.getInt(column_name));
					}
					else if(rsmd.getColumnType(i)==java.sql.Types.BOOLEAN){
						obj.put(column_name, rs.getBoolean(column_name));
					}
					else if(rsmd.getColumnType(i)==java.sql.Types.BLOB){
						obj.put(column_name, rs.getBlob(column_name));
					}
					else if(rsmd.getColumnType(i)==java.sql.Types.DOUBLE){
						obj.put(column_name, rs.getDouble(column_name)); 
					}
					else if(rsmd.getColumnType(i)==java.sql.Types.FLOAT){
						obj.put(column_name, rs.getFloat(column_name));
					}
					else if(rsmd.getColumnType(i)==java.sql.Types.INTEGER){
						obj.put(column_name, rs.getInt(column_name));
					}
					else if(rsmd.getColumnType(i)==java.sql.Types.NVARCHAR){
						obj.put(column_name, rs.getNString(column_name));
					}
					else if(rsmd.getColumnType(i)==java.sql.Types.VARCHAR){
						obj.put(column_name, rs.getString(column_name));
					}
					else if(rsmd.getColumnType(i)==java.sql.Types.TINYINT){
						obj.put(column_name, rs.getInt(column_name));
					}
					else if(rsmd.getColumnType(i)==java.sql.Types.SMALLINT){
						obj.put(column_name, rs.getInt(column_name));
					}
					else if(rsmd.getColumnType(i)==java.sql.Types.DATE){
						obj.put(column_name, rs.getDate(column_name));
					}
					else if(rsmd.getColumnType(i)==java.sql.Types.TIMESTAMP){
						obj.put(column_name, rs.getTimestamp(column_name));   
					}
					else{
						obj.put(column_name, rs.getObject(column_name));
					}
				}

				jArray.put(obj);
			}

		}

		connection.close();



		return jArray;
	}

	public JSONArray getGridCellListJSON() throws NamingException, SQLException, JSONException {

		JSONArray jArray = null;
		Connection connection = getConnection();
		//		PreparedStatement preparedStatement = connection.prepareStatement("SELECT gridcell.gridcell_id,gridcell.cellcategory_id,gridcell.location_id,location.x,location.y,location.z,location.lat,location.lon FROM gridcell INNER JOIN location ON gridcell.location_id=location.locationID;");
		PreparedStatement preparedStatement = connection.prepareStatement("SELECT g.gridcell_id,g.location_id,l.x,l.y,l.z,l.lat,l.lon,cc.cellcat FROM gridcell g INNER JOIN location l ON g.location_id=l.locationID INNER JOIN cellcategory cc ON g.cellcategory_id=cc.cellcategoryID;");

		if (preparedStatement.execute()) {
			ResultSet rs = preparedStatement.getResultSet();
			ResultSetMetaData rsmd = rs.getMetaData();
			jArray = makeJSONArray(rsmd,rs);
		}
		connection.close();

		return jArray;
	}


	public JSONArray getCheckinBeanJSONArray() throws SQLException, JSONException {

		JSONArray jArray = null;

		Connection con = getConnection();
		PreparedStatement preparedStatement = 
				con.prepareStatement("SELECT cm.name,l.urlstring,c.mytimestamp " +
						"FROM collectiblemaster cm " +
						"INNER JOIN livecollectible lc " +
						"ON cm.collectiblemaster_id=lc.collectiblemaster_id " +
						"INNER JOIN location l " +
						"ON lc.location_id=l.location_id " + 
						"INNER JOIN checkin c " +
						"ON l.urlstring=c.urlstring");
		preparedStatement.execute();
		ResultSet rs = preparedStatement.getResultSet();
		ResultSetMetaData rsmd = rs.getMetaData();

		try {
			if(rs.next()) {
				return makeJSONArray(rsmd,rs);
			}
		} finally {
			con.close();
		}
		return null;
	}

//	public JSONArray getCheckinBeanJSONArray2() throws SQLException, JSONException {
//
//		JSONArray jArray = null;
//
//		Connection con = getConnection();
//		PreparedStatement preparedStatement = 
//				con.prepareStatement("SELECT cm.name,l.urlstring,c.mytimestamp " +
//						"FROM collectiblemaster cm " +
//						"INNER JOIN livecollectible lc " +
//						"ON cm.collectiblemaster_id=lc.collectiblemaster_id " +
//						"INNER JOIN location l " +
//						"ON lc.location_id=l.location_id " + 
//						"INNER JOIN checkin c " +
//						"ON l.urlstring=c.urlstring");
//		preparedStatement.execute();
//		ResultSet rs = preparedStatement.getResultSet();
//		ResultSetMetaData rsmd = rs.getMetaData();
//
//		try {
//			if(rs.next()) {
//				return makeJSONArray2(rsmd,rs);
//			}
//		} finally {
//			con.close();
//		}
//		return null;
//	}

	
	
	public JSONArray makeJSONArray(ResultSetMetaData rsmd, ResultSet rs) throws SQLException, JSONException {
		JSONArray jArray = new JSONArray();

		while(rs.next()) {
			int numColumns = rsmd.getColumnCount();
			JSONObject obj = new JSONObject();
//			System.out.println("numofcols: " + numColumns);
			for (int i=1; i<numColumns+1; i++) {
				//				String column_name = rsmd.getColumnName(i);
				String colname = rsmd.getColumnName(i);
				String column_name = stripColumnName(colname);

				if(rsmd.getColumnType(i)==java.sql.Types.ARRAY){
					obj.put(column_name, rs.getArray(colname));
				}
				else if(rsmd.getColumnType(i)==java.sql.Types.BIGINT){
					obj.put(column_name, rs.getInt(colname));
				}
				else if(rsmd.getColumnType(i)==java.sql.Types.BOOLEAN){
					obj.put(column_name, rs.getBoolean(colname));
				}
				else if(rsmd.getColumnType(i)==java.sql.Types.BLOB){
					obj.put(column_name, rs.getBlob(colname));
				}
				else if(rsmd.getColumnType(i)==java.sql.Types.DOUBLE){
					obj.put(column_name, rs.getDouble(colname)); 
				}
				else if(rsmd.getColumnType(i)==java.sql.Types.FLOAT){
					obj.put(column_name, rs.getFloat(colname));
				}
				else if(rsmd.getColumnType(i)==java.sql.Types.INTEGER){
					obj.put(column_name, rs.getInt(colname));
				}
				else if(rsmd.getColumnType(i)==java.sql.Types.NVARCHAR){
					obj.put(column_name, rs.getNString(colname));
				}
				else if(rsmd.getColumnType(i)==java.sql.Types.VARCHAR){
					obj.put(column_name, rs.getString(colname));
				}
				else if(rsmd.getColumnType(i)==java.sql.Types.TINYINT){
					obj.put(column_name, rs.getInt(colname));
				}
				else if(rsmd.getColumnType(i)==java.sql.Types.SMALLINT){
					obj.put(column_name, rs.getInt(colname));
				}
				else if(rsmd.getColumnType(i)==java.sql.Types.DATE){
					obj.put(column_name, rs.getDate(colname));
				}
				else if(rsmd.getColumnType(i)==java.sql.Types.TIMESTAMP){
					obj.put(column_name, rs.getTimestamp(colname));   
				}
				else{
					obj.put(column_name, rs.getObject(colname));
				}
			}

			jArray.put(obj);
		}

		return jArray;
	}
	
//	public JSONArray makeJSONArray2(ResultSetMetaData rsmd, ResultSet rs) throws SQLException, JSONException {
//		JSONArray jArray = new JSONArray();
//
//		while(rs.next()) {
//			int numColumns = rsmd.getColumnCount();
//			JSONObject obj = new JSONObject();
////			System.out.println("numofcols: " + numColumns);
//			for (int i=1; i<numColumns+1; i++) {
//				//				String column_name = rsmd.getColumnName(i);
//				String colname = rsmd.getColumnName(i);
//				String column_name = stripColumnName(colname);
//
//				if(rsmd.getColumnType(i)==java.sql.Types.ARRAY){
//					obj.put(column_name, rs.getArray(colname));
//				}
//				else if(rsmd.getColumnType(i)==java.sql.Types.BIGINT){
//					obj.put(column_name, rs.getInt(colname));
//				}
//				else if(rsmd.getColumnType(i)==java.sql.Types.BOOLEAN){
//					obj.put(column_name, rs.getBoolean(colname));
//				}
//				else if(rsmd.getColumnType(i)==java.sql.Types.BLOB){
//					obj.put(column_name, rs.getBlob(colname));
//				}
//				else if(rsmd.getColumnType(i)==java.sql.Types.DOUBLE){
//					obj.put(column_name, rs.getDouble(colname)); 
//				}
//				else if(rsmd.getColumnType(i)==java.sql.Types.FLOAT){
//					obj.put(column_name, rs.getFloat(colname));
//				}
//				else if(rsmd.getColumnType(i)==java.sql.Types.INTEGER){
//					obj.put(column_name, rs.getInt(colname));
//				}
//				else if(rsmd.getColumnType(i)==java.sql.Types.NVARCHAR){
//					obj.put(column_name, rs.getNString(colname));
//				}
//				else if(rsmd.getColumnType(i)==java.sql.Types.VARCHAR){
//					obj.put(column_name, rs.getString(colname));
//				}
//				else if(rsmd.getColumnType(i)==java.sql.Types.TINYINT){
//					obj.put(column_name, rs.getInt(colname));
//				}
//				else if(rsmd.getColumnType(i)==java.sql.Types.SMALLINT){
//					obj.put(column_name, rs.getInt(colname));
//				}
//				else if(rsmd.getColumnType(i)==java.sql.Types.DATE){
//					obj.put(column_name, rs.getDate(colname));
//				}
//				else if(rsmd.getColumnType(i)==java.sql.Types.TIMESTAMP){
//					Timestamp ts = rs.getTimestamp(colname);
//					Calendar c = Calendar.getInstance();
//					
//					c.setTime(ts);
//					c.add(Calendar.HOUR, +10);
//					Date date = c.getTime();             
//					SimpleDateFormat checkinDate = new SimpleDateFormat("EEE, MMM d, ''yy");
//					String cDate = checkinDate.format(date); 
//					System.out.println(cDate);
//					SimpleDateFormat checkinTime = new SimpleDateFormat("h:mm a");
//					String cTime = checkinTime.format(date); 
//					//obj.put("time", rs.getTimestamp(colname));   
////					obj.put("checkinTime", checkinTime);
////					obj.put("checkinDate", checkinDate);
//					obj.put("checkinTime", cTime);
//					obj.put("checkinDate", cDate);
//				}
//				else{
//					obj.put(column_name, rs.getObject(colname));
//				}
//			}
//
//			jArray.put(obj);
//		}
//
//		return jArray;
//	}

	private String stripColumnName(String s) {
		String str = null;
		int startPos = s.indexOf(".");
		str = s.substring(startPos + 1);
		System.out.println(str);


		return str;
	}

	public List<String> getMasterGridList() throws NamingException, SQLException {
		List<String> l = new ArrayList<String>();
		int colCount = 0;

		try {

			Connection connection = getConnection();
			PreparedStatement preparedStatement = connection.prepareStatement("select * from gridcellmaster limit 100");

			if (preparedStatement.execute()) {
				ResultSet rs = preparedStatement.getResultSet();
				ResultSetMetaData md = rs.getMetaData();
				colCount = md.getColumnCount();


				while(rs.next() !=false) {
					String current = "";
					int start = 0;
					for (int x = 1; x <= colCount; x ++) {
						if (start > 0) {
							current += ",";
						}
						start ++;
						current += rs.getString(x);
					}
					l.add(current);
				}

			}

			connection.close();

		} catch (SQLException e) {
			throw e;
		}


		return l;
	}

	public String getMasterGridListXML() throws NamingException, SQLException, ParserConfigurationException, TransformerException {		

		List<String> l = new ArrayList<String>();
		int colCount = 0;
		ResultSet rs = null;
		Connection connection = null;
		StreamResult sr = null;
		try {

			connection = getConnection();
			PreparedStatement preparedStatement = connection.prepareStatement("select * from gridcellmaster limit 100");

			if (preparedStatement.execute()) {
				rs = preparedStatement.getResultSet();
				ResultSetMetaData md = rs.getMetaData();
				colCount = md.getColumnCount();

				DocumentBuilderFactory docFactory = DocumentBuilderFactory.newInstance();
				DocumentBuilder docBuilder = docFactory.newDocumentBuilder();
				Document doc = docBuilder.newDocument();

				Element results = doc.createElement("Results");  
				doc.appendChild(results); 

				while (rs.next())  
				{  
					Element row = doc.createElement("Cell");  
					results.appendChild(row);  

					for (int i = 1; i <= colCount; i++)  
					{  
						String columnName = md.getColumnName(i);  
						Object value      = rs.getObject(i);  

						Element node = doc.createElement(columnName);  
						node.appendChild(doc.createTextNode(value.toString()));  
						row.appendChild(node);  
					}  
				} 

				DOMSource domSource = new DOMSource(doc);
				TransformerFactory tf = TransformerFactory.newInstance();
				Transformer transformer = tf.newTransformer();
				transformer.setOutputProperty(OutputKeys.METHOD, "xml");
				transformer.setOutputProperty(OutputKeys.ENCODING,"ISO-8859-1");
				transformer.setOutputProperty
				("{http://xml.apache.org/xslt}indent-amount", "4");
				transformer.setOutputProperty(OutputKeys.INDENT, "yes");
				java.io.StringWriter sw = new java.io.StringWriter();
				sr = new StreamResult(sw);
				transformer.transform(domSource, sr);

			}

		} catch (SQLException e) {
			throw e;
		}

		connection.close();
		return sr.getWriter().toString();
	}

	private Connection getConnection() {

		Context ctx;
		Connection connection = null;
		try {
			ctx = new InitialContext();
			DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/cabbagedb");
			connection = ds.getConnection();
		} catch (NamingException e) {
			throw new RuntimeException(e);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

		return connection;

		//		Context ctx = new InitialContext();
		//		DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/cabbagedb");
		//		Connection connection = ds.getConnection();
		//		return connection;
	}

	//	public void readFromFile(InputStream inputstream) throws ServletException {
	//
	//		Scanner sc = null;
	//		sc = new Scanner(inputstream);
	//		//sc = new Scanner("newData.txt");
	//
	//		while (sc.hasNext()) {
	//
	//			String tokenString = sc.next();
	//			String splitArray[] = tokenString.split(",");
	//
	//			int z = Integer.parseInt(splitArray[0]);
	//			int y = Integer.parseInt(splitArray[1]);
	//			int startX = Integer.parseInt(splitArray[2]);
	//			int endX = Integer.parseInt(splitArray[3]);
	//			String cellCategory = splitArray[4];
	//			
	//			int cellID = 0;
	//			if (cellCategory.equals("p")) {
	//				cellID = 1;
	//			}
	//			if (cellCategory.equals("b")) {
	//				cellID = 2;
	//			}
	//			if (cellCategory.equals("c")) {
	//				cellID = 3;
	//			}
	//			if (cellCategory.equals("r")) {
	//				cellID = 4;
	//			}
	//			if (cellCategory.equals("n")) {
	//				cellID = 5;
	//			}
	//
	////			for (int xCounter = startX; xCounter < endX + 1; xCounter ++ ) {
	////				insertIntoMasterGrid(z,y,startX,endX,cellID);
	////			}
	//			
	//			insertIntoMasterGrid(z,y,startX,endX,cellID);
	//
	//		}
	//
	//	}
	//
	//	public void insertIntoMasterGrid(int z,int y,int startx,int endx, int cellCatID) throws ServletException {
	//		try {
	//			Connection connection = getConnection();
	//			try {
	//				PreparedStatement preparedStatement = connection.prepareStatement("insert into gridcellmaster (z,y,startx,endx,cellcategoryID) values (?,?,?,?,?)");
	//				preparedStatement.setInt(1, z);
	//				preparedStatement.setInt(2, y);
	//				preparedStatement.setInt(3, startx);
	//				preparedStatement.setInt(4, endx);
	//				preparedStatement.setInt(5, cellCatID);
	//				preparedStatement.execute();
	//			} finally {
	//				connection.close();
	//			}
	//		} catch (Exception e) {
	//			throw new ServletException(e);
	//		}
	//	}


}
